import os
import pymysql
import time
import datetime
import json
from apscheduler.schedulers.blocking import BlockingScheduler
from dateutil.relativedelta import relativedelta

# 源数据库表
source_ip = ''
source_port = ''
source_db_name = ''
source_name = ''
source_password = ''
source_table_name = ''
# 备份表文件
dest_ip = ''
dest_port = ''
dest_db_name = ''
dest_name = ''
dest_password = ''
dest_table_name = ''
# 归档条件
month = 1
where_sql_and = ''


# 执行归档
def archiving():
    try:
        # 时间导出条件
        date_condition = (datetime.datetime.now() + relativedelta(
            months=-month)).strftime("%Y-%m-%d") + ' 00:00:00'
        # pt-archiver 归档命令
        archiving_sql = f'pt-archiver --txn-size=1000 --limit=1000 --charset=UTF8 --why-quit  --bulk-insert --bulk-delete --statistics --no-version-check --analyze=ds\
                        --source h={source_ip},L=1,P={source_port},u={source_name},p={source_password},D={source_db_name},t={source_table_name} \
                        --dest h={dest_ip},L=1,P={dest_port},u={dest_name},p={dest_password},D={dest_db_name},t={dest_table_name},i=created_time_index \
                        --where="created_time >\'{date_condition}\' {where_sql_and}"'

        print("归档命令:" + archiving_sql)
        res = os.system(archiving_sql)
        print('归档执行完毕:{}'.format(res))
    except RuntimeError as identifier:
        print(identifier)


# 创建新表
def createTab(source_db, dest_db):
    try:
        print(f'归档数据开始,新表名:{dest_table_name}')
        cursor_source = source_db.cursor()
        cursor_source.execute(f"SHOW CREATE TABLE {source_table_name}")
        table_sql = cursor_source.fetchall()[0][1]
        # 获取目标库
        cursor_dest = dest_db.cursor()
        # 新建表
        cursor_dest.execute(f"drop table if exists {dest_table_name}")
        cursor_dest.execute(table_sql)
        cursor_dest.execute(
            f"alter table {source_table_name} rename {dest_table_name}")
        cursor_dest.execute(
            f"ALTER TABLE `{dest_db_name}`.`{dest_table_name}` ADD INDEX `created_time_index`(`created_time`);"
        )
    except RuntimeError as identifier:
        print(identifier)


# 转换参数
def conversion(data: dict):
    glo = globals()
    glo['source_ip'] = data['source_ip']
    glo['source_port'] = data['source_port']
    glo['source_db_name'] = data['source_db_name']
    glo['source_name'] = data['source_name']
    glo['source_password'] = data['source_password']
    glo['dest_ip'] = data['dest_ip']
    glo['dest_port'] = data['dest_port']
    glo['dest_db_name'] = data['dest_db_name']
    glo['dest_name'] = data['dest_name']
    glo['dest_password'] = data['dest_password']
    glo['month'] = data['month']


# 根据配置文件读取 获取需要归档的数据库表
def read_configuration():
    path = os.path.abspath(os.path.dirname(__file__))
    with open(path + '/configuration.json', 'r', encoding='utf-8') as fp:
        data = json.load(fp)
        return data


# 执行任务体
def job():
    try:
        data = read_configuration()
        for item in data:
            # 转换配置文件参数
            conversion(item)
            # 获取源数据源和目标数据源
            source_db = pymysql.connect(source_ip, source_name,
                                        source_password, source_db_name)
            dest_db = pymysql.connect(dest_ip, dest_name, dest_password,
                                      dest_db_name)
            # 获取到多数据库配置
            tables = item['tables']
            for table in tables:
                # 源数据
                global source_table_name
                # 目标数据
                global dest_table_name
                # 附加归档条件
                global where_sql_and
                source_table_name = table['table_name']
                dest_table_name = f'{source_table_name}_history_{time.strftime("%Y%m%d", time.localtime())}'
                where_sql_and = table['where_sql_and']
                if not where_sql_and:
                    where_sql_and = ''
                createTab(source_db, dest_db)
                archiving()
            source_db.close()
            dest_db.close()
    except RuntimeError as err:
        print(err)


# 创建定时任务
scheduler = BlockingScheduler()
scheduler.add_job(job, 'cron', month='*', day='1', hour='11', minute='59')
scheduler.start()
